﻿create PROCEDURE Sync.SP_Customers_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @Customers_Temp TABLE 
		(
			CustomerID UniqueIdentifier,
			[CustomerName] nvarchar(50),
				[Birthdate] datetime,
				[Gender] tinyint,
				[SSN] nvarchar(50),
				[SSNCreationDate] datetime,
				[SSNCreationLocationID] uniqueidentifier,
				[BirthLocationID] uniqueidentifier,
				[Address] nvarchar(MAX),
				[CurrentJob] nvarchar(50),
				[MilitaryStatusID] uniqueidentifier,
				[SocialStatusID] uniqueidentifier,
				[EducationalLevelID] uniqueidentifier,
				[MilitaryPostpondingDate] datetime,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @Customers_Temp 
	   SELECT	Tbl.Col.value('CustomerID[1]','uniqueidentifier') CustomerID,
                Tbl.Col.value('CustomerName[1]', 'nvarchar(50)') [CustomerName],
Tbl.Col.value('Birthdate[1]', 'datetime') [Birthdate],
Tbl.Col.value('Gender[1]', 'tinyint') [Gender],
Tbl.Col.value('SSN[1]', 'nvarchar(50)') [SSN],
Tbl.Col.value('SSNCreationDate[1]', 'datetime') [SSNCreationDate],
Tbl.Col.value('SSNCreationLocationID[1]', 'uniqueidentifier') [SSNCreationLocationID],
Tbl.Col.value('BirthLocationID[1]', 'uniqueidentifier') [BirthLocationID],
Tbl.Col.value('Address[1]', 'nvarchar(MAX)') [Address],
Tbl.Col.value('CurrentJob[1]', 'nvarchar(50)') [CurrentJob],
Tbl.Col.value('MilitaryStatusID[1]', 'uniqueidentifier') [MilitaryStatusID],
Tbl.Col.value('SocialStatusID[1]', 'uniqueidentifier') [SocialStatusID],
Tbl.Col.value('EducationalLevelID[1]', 'uniqueidentifier') [EducationalLevelID],
Tbl.Col.value('MilitaryPostpondingDate[1]', 'datetime') [MilitaryPostpondingDate],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/Customers') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[Customers] ([CustomerID],[CustomerName],[Birthdate],[Gender],[SSN],[SSNCreationDate],[SSNCreationLocationID],[BirthLocationID],[Address],[CurrentJob],[MilitaryStatusID],[SocialStatusID],[EducationalLevelID],[MilitaryPostpondingDate])
	SELECT t.[CustomerID],t.[CustomerName],t.[Birthdate],t.[Gender],t.[SSN],t.[SSNCreationDate],t.[SSNCreationLocationID],t.[BirthLocationID],t.[Address],t.[CurrentJob],t.[MilitaryStatusID],t.[SocialStatusID],t.[EducationalLevelID],t.[MilitaryPostpondingDate]
	FROM  @Customers_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.Customers_Tracking s
        WHERE t.CustomerID = s.CustomerID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[Customers]
	SET [Customers].[CustomerName] = t.[CustomerName],[Customers].[Birthdate] = t.[Birthdate],[Customers].[Gender] = t.[Gender],[Customers].[SSN] = t.[SSN],[Customers].[SSNCreationDate] = t.[SSNCreationDate],[Customers].[SSNCreationLocationID] = t.[SSNCreationLocationID],[Customers].[BirthLocationID] = t.[BirthLocationID],[Customers].[Address] = t.[Address],[Customers].[CurrentJob] = t.[CurrentJob],[Customers].[MilitaryStatusID] = t.[MilitaryStatusID],[Customers].[SocialStatusID] = t.[SocialStatusID],[Customers].[EducationalLevelID] = t.[EducationalLevelID],[Customers].[MilitaryPostpondingDate] = t.[MilitaryPostpondingDate] 
	FROM [dbo].[Customers] s JOIN @Customers_Temp t 
    ON t.CustomerID = s.CustomerID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.Customers_Tracking r
				  WHERE t.CustomerID = r.CustomerID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[Customers]
    FROM @Customers_Temp t JOIN [dbo].[Customers] s
    ON t.CustomerID = s.CustomerID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














